package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import model.Notice;

public class NoticeDao {

	public NoticeDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	//String pass = "webclass";


	// 특정 study내의 notice 리스트 불러오기
	public ArrayList<Notice> getAllNoticeList(int studyId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Notice> noticeList = new ArrayList<Notice>();
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select noticeid, sid, notice.title, memid, notice.createdTime from notice,user,study where studyid=sid and notice.uid=userid and sid=? order by noticeId desc");
			pstmt.setInt(1, studyId);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int noticeId = rs.getInt("noticeId");
				int sid = rs.getInt("sid");
				String title = rs.getString("title").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				noticeList.add(new Notice(noticeId, sid, title, memid,
						createdTime));
			}

			return noticeList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return noticeList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// notice 내용 불러오기
	public Notice getNoticeInfo(int noticeId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Notice notice = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select noticeid, sid, title, content, memid, createdTime, updatedTime from notice,user where uid=userid and noticeid=?");
			pstmt.setInt(1, noticeId);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int nid = rs.getInt("noticeId");
				int sid = rs.getInt("sid");
				String title = rs.getString("title").trim();
				String content = rs.getString("content").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				Timestamp updatedTime = rs.getTimestamp("updatedTime");
				notice = new Notice(nid, sid, title, content, memid,
						createdTime, updatedTime);
			}
			return notice;

		} catch (Exception ex) {
			ex.printStackTrace();
			return notice;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// notice 작성
	public void insertNoticeInfo(Notice newNotice) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into notice(sid, title, content, uid, createdTime) values(?,?,?,?,sysdate())");
			pstmt.setInt(1, newNotice.getSid());
			pstmt.setString(2, newNotice.getTitle());
			pstmt.setString(3, newNotice.getContent());
			pstmt.setInt(4, newNotice.getUid());

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// notice 수정
	public void updateNoticeInfo(Notice updateNotice) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update notice set title=?, content=?, updatedTime=sysdate() where noticeId=?");
			pstmt.setString(1, updateNotice.getTitle());
			pstmt.setString(2, updateNotice.getContent());
			pstmt.setInt(3, updateNotice.getNoticeId());

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

}